<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <title>WEBSQL-SQL执行窗口</title>
    <link rel="shortcut icon" type="image/x-icon" th:href="@{/static/img/favicon.ico}" media="screen"/>
    <link rel="stylesheet" th:href="@{/static/css/layui.css}" media="all"/>
    <link rel="stylesheet" th:href="@{/static/code/codemirror.css}" media="all"/>
    <link rel="stylesheet" th:href="@{/static/code/show-hint.css}" media="all"/>
    <link rel="stylesheet" th:href="@{/static/code/highlight.min.css}" media="all">
    <style>
        .sqlText {
            border: 1px solid;
            border-radius: 12px;
            box-shadow: 2px 2px 5px #bbb;
            font-size: 20px;
            margin: 5px;
        }

        .CodeMirror {
            border-radius: 12px;
        }

        .sqlTab {
            border: 1px solid;
            border-radius: 12px;
            box-shadow: 2px 2px 5px #bbb;
            font-size: 20px;
            margin: 5px;
            position: relative;
        }

        .menus {
            border: 1px solid;
            border-radius: 12px;
            box-shadow: 2px 2px 5px #bbb;
            margin: 5px;
        }

        .layui-form-item {
            margin-bottom: 5px;
            margin-top: 5px;
        }

        .layui-tab-content {
            margin-top: -36px;
        }

        .layui-tab {
            margin: 0;
        }

        .nav-container {
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            background-color: #fff;
            box-shadow: 0 3px 5px rgba(0, 0, 0, .1);
            z-index: 999;
            transition: all 0.2s ease-in-out;
        }

        pre.CodeMirror-placeholder {
            color: #a19c9c;
            font-size: 1rem;
        }


    </style>
</head>
<body>

<div class="menus" style="margin:5px;">
    <form class="layui-form">
        <div class="layui-form-item">
            <label class="layui-form-label">数据源:</label>
            <div class="layui-input-inline">
                <select name="dbtype" id="dbtype" lay-filter="dbtype" lay-search="" lay-affix="clear"></select>
            </div>
            <div class="layui-input-inline">
                <select name="sqlTexts" id="sqlTexts" lay-filter="sqlTexts" lay-search="" lay-affix="clear"></select>
            </div>
            <div class="">
                <button class="layui-btn layui-btn-primary layui-border-green " id="execute" lay-submit
                        lay-filter="execute">
                    <i class="layui-icon layui-icon-play">&nbsp;</i>执行F8
                </button>
                <button id="newSqlOpen" onclick="window.open(ctx+'sqlManager/sqlPage');return false"
                        class="layui-btn layui-btn-primary layui-border-blue">
                    <i class="layui-icon layui-icon-layer">&nbsp;</i>大窗口F7
                </button>
                <button id="saveSqlText" class="layui-btn layui-btn-primary layui-border-orange" lay-submit
                        lay-filter="saveSqlText">
                    <i class="layui-icon layui-icon-add-1">&nbsp;</i>保存F9
                </button>
                <button id="showSqlDiv" class="layui-btn layui-btn-primary layui-border-purple" lay-submit
                        lay-filter="showSqlDiv">
                    <i class="layui-icon layui-icon-up">&nbsp;</i>收起
                </button>
            </div>
        </div>
    </form>
</div>

<div class="sqlText">
    <textarea id='sqlText' style="width: 100%;height: 500px;align-content: center;"></textarea>
</div>

<div id="msgDiv" class="layui-row sqlTab" style="display: none;">
    <div style="font-size: 15px;color: #843534;display:block;">
        <span id="msgSpan"
              style="margin-left: 10px;display:block;width:95%;word-wrap:break-word;white-space:normal;"></span>
    </div>
</div>

<div class="layui-row sqlTab" style="display: none;" id="sqlTableDiv">
    <div class="layui-tab layui-tab-brief" lay-filter="sqlTableTab" lay-allowclose="true">
        <ul class="layui-tab-title" id="lists">
        </ul>
        <div class="layui-tab-content" id="listsContent">
        </div>
    </div>
</div>

<div id="view-open-table-wrapper" style="display: none;padding: 10px;" class="hidden-scrollbar">
</div>
<script th:src="@{/static/layui.js}"></script>
<script th:replace="~{base::ctx}"></script>
<script th:src="@{/static/lay-config.js}"></script>
<script th:src="@{/static/code/codemirror.js}"></script>
<script th:src="@{/static/code/matchbrackets.js}"></script>
<script th:src="@{/static/code/sql.js}"></script>
<script th:src="@{/static/code/show-hint.js}"></script>
<script th:src="@{/static/code/sql-hint.js}"></script>
<script th:src="@{/static/code/sql-formatter.min.cjs}"></script>
<script th:src="@{/static/code/placeholder.js}"></script>
<script th:src="@{/static/code/highlight.min.js}"></script>
<script th:replace="~{tableSqlPageMeta}"></script>
<script>
    layui.use(['jquery', 'layer', 'form', 'element', 'laySelect', 'table', 'dropdown', 'util', 'laytpl', 'code'], function () {
        const layer = layui.layer;
        const form = layui.form;
        const $ = layui.jquery;
        const element = layui.element;
        const select = layui.laySelect;
        const table = layui.table;
        const dropdown = layui.dropdown;
        const util = layui.util;
        const laytpl = layui.laytpl;
        const code = layui.code;
        let pageVisibility;  //页面隐藏

        /**
         * 编辑器初始化
         * @type {CodeMirror}
         */
        const editor = CodeMirror.fromTextArea(document.getElementById("sqlText"), {
            mode: {
                name: "text/x-plsql"
            },
            matchBrackets: true,
            styleActiveLine: true,
            indentWithTabs: true,
            smartIndent: true,
            lineNumbers: true,
            spellcheck: true,
            lineWrapping: true,
            resetSelectionOnContextMenu: true,
            extraKeys: {
                'Ctrl': 'autocomplete',
                Tab: function (cm) {
                    let spaces = Array(cm.getOption('indentUnit') + 1).join(' ');
                    cm.replaceSelection(spaces);
                },
                'Alt-X': function (cm) {
                    hintMeta(cm);
                },
                'Alt-C': function (cm) {
                    formatSql(cm);
                },
                'Alt-Z': function (cm) {
                    showTableSql(cm);
                }
            },
            hintOptions: {completeSingle: false},
            placeholder: '输入您的要执行的SQL内容... \n\n\n' +
                '快捷键：\n' +
                '鼠标选中【表名】+Alt+X  查看元数据 \n' +
                '鼠标选中【表名】+Alt+Z  查看建表语句 \n' +
                'Alt+C      格式化内容 \n' +
                'Ctrl       自动提示 \n' +
                'F7         打开新窗口 \n' +
                'F8         执行所有SQL \n' +
                'F9         保存SQL内容 \n' +
                '选中内容+F8 选中SQL执行 \n'
        });

        /**
         * 自动提示
         */
        editor.on('keypress', () => {
            editor.showHint();
        });


        /**
         * 展示数据源
         */
        let activeDataSource;//选中的数据源
        select.render({
            elem: "#dbtype",
            url: ctx + 'dataSourceManager/findDataSourceList',
            select: 10000,
            title: '请选择数据源',
            format: function (data) {
                return parseSelectData(data);
            },
            success: function (data, activeData) {
                //默认选中的数据源需要单独赋值，不会走onSelect
                activeDataSource = activeData == null ? null : activeData[0].code;
                editorHintSettings();
            },
            onselect: function (data) {
                activeDataSource = data;
                editorHintSettings();
            }
        });

        /**
         * 选中内容中获取表名
         * @param cm
         * @returns {*}
         */
        function getSelectTable(cm) {
            let table = cm.getSelection();
            if (table.length === 0 || activeDataSource === null) {
                throw '请选中表名且选中数据源后，在使用快捷键~';
            }
            return table;
        }

        /**
         * 提示元数据
         */
        function hintMeta(cm) {
            post(ctx + "sqlManager/findMetaTable?database=" + activeDataSource + "&table=" + getSelectTable(cm), null, (res) => {
                if (res.code != 200 || res.data === null) {
                    console.warn("无法提示元数据信息!" + res.msg);
                    return false;
                }
                laytpl($("#view-tale-meta-tpl").html()).render(res.data, function (data) {
                    $("#view-open-table-wrapper").html(data);
                    layer.open({
                        type: 1,
                        area: ['630px', '540px'],
                        title: false,
                        anim: 'slideUp',
                        closeBtn: 0,
                        shadeClose: true,
                        scrollbar: false,
                        content: $('#view-open-table-wrapper')
                    });
                });
            });
        }


        /**
         * 格式化SQL文本
         */
        function formatSql(cm) {
            let config = {
                "language": "sql",
                "tabWidth": 3,
                "keywordCase": "upper"
            };
            let newContent = window.sqlFormatter.format(cm.getValue(), config);
            cm.setValue(newContent);
        }

        /**
         * 提示建表语句
         * @param cm
         * @returns {boolean}
         */
        function showTableSql(cm) {
            post(ctx + "sqlManager/showTableSql?database=" + activeDataSource + "&table=" + getSelectTable(cm), null, (res) => {
                if (res.code != 200 || res.data === null) {
                    console.warn("无法展示建表语句信息!" + res.msg);
                    return false;
                }
                laytpl($("#table-sql-code-tpl").html()).render(res.data, function (data) {
                    $("#view-open-table-wrapper").html(data);
                    code({
                        elem: '.sql-code',
                        lang: 'SQL',
                        highlighter: "hljs",
                        encode: true,
                        langMarker:true,
                        codeRender: function (code, opts) {
                            return hljs.highlight(code, {language: opts.lang}).value;
                        }
                    });
                    layer.open({
                        type: 1,
                        area: ['630px', '540px'],
                        title: false,
                        anim: 'slideUp',
                        closeBtn: 0,
                        shadeClose: true,
                        scrollbar: false,
                        content: $('#view-open-table-wrapper'),
                    });
                });
            });
        }

        /**
         * 设置编辑器提示表名及字段功能
         */
        function editorHintSettings() {
            post(ctx + "sqlManager/findTableField?database=" + activeDataSource, null, (res) => {
                if (res.code != 200 || res.data === null) {
                    console.warn("获取表名及字段失败,无法提供编辑器提示表名功能!" + res.msg);
                    return false;
                }
                editor.setOption('hintOptions', {
                    completeSingle: false,
                    tables: res.data
                });
            });
        }

        /**
         * 展示sql保存记录
         */
        function sqlTextSelect() {
            select.render({
                elem: "#sqlTexts",
                url: ctx + 'sqlManager/querySqlTextSelect',
                select: 10000,
                title: '请选择SQL文本',
                format: function (data) {
                    return parseSelectData(data);
                },
                success: function (data, activeData) {
                },
                onselect: function (data) {
                    let oldContent = editor.getValue();
                    if (oldContent != "" || oldContent != null) {
                        data = oldContent + '\n' + data;
                    }
                    editor.setValue(data);
                }
            });
        }

        sqlTextSelect();

        /**
         * 保存SQL文本
         */
        form.on('submit(saveSqlText)', function (data) {
            const sqlContent = editor.getValue().trim();
            if (sqlContent == "" || sqlContent == null) {
                layer.msg("SQL语句为空，无需保存哦~", {icon: 2});
                return false;
            }
            let openConfig = {
                type: 1,
                skin: 'layui-layer-win10',
                shade: 0.3,
                area: '520px',
                title: '*保存SQL文本',
                closeBtn: 0,
                formType: 0,
                anim: 'slideDown',
                shadeClose: true,
                success: function () {
                    $(".layui-layer-content .layui-layer-input").toggleClass("layui-input").attr('placeHolder', '请起个名字吧...');
                }
            };
            layer.prompt(openConfig, function (data, index) {
                if (data === '' || data == null) {
                    layer.msg('名字不能为空~', {icon: 2});
                    return false;
                }
                layer.close(index);
                post(ctx + "sqlManager/saveSqlText", {'sqlText': sqlContent, 'title': data}, (res) => {
                    layer.msg(res.msg, {icon: res.code === 200 ? 1 : 2}, (res) => {
                        sqlTextSelect();
                    });
                })
            });
            return false;
        });


        /**
         * sql执行窗口隐藏
         */
        form.on('submit(showSqlDiv)', function (data) {
            let display = $('.sqlText').css('display');
            if (display === 'none') {
                $('.sqlText').css('display', 'block');
                $("#msgDiv").css('display', 'block');
                $("#showSqlDiv").html('<i class="layui-icon layui-icon-up">&nbsp;</i>收起');
            } else {
                $('.sqlText').css('display', 'none');
                $("#msgDiv").css('display', 'none');
                $("#showSqlDiv").html('<i class="layui-icon layui-icon-down">&nbsp;</i>展开');
            }
            return false;
        });


        /**
         * 执行SQL
         */
        form.on('submit(execute)', function (data) {
            $("#msgDiv").show();
            $("#msgSpan").text("");

            if (activeDataSource === "" || activeDataSource == null) {
                layer.msg("请先选择数据源~", {icon: 2});
                $("#msgDiv").show();
                $("#msgSpan").text("ERROR:请先选择数据源~");
                return false;
            }
            let sqlContent = editor.getValue().trim();
            if (sqlContent === "" || sqlContent == null) {
                layer.msg("请输入要执行的SQL~", {icon: 2});
                $("#msgDiv").show();
                $("#msgSpan").text("ERROR:请输入要执行的SQL~");
                return false;
            }
            let pitchTxt = editor.getSelection();
            if (pitchTxt.length !== 0) {
                sqlContent = pitchTxt;
            }
            let intervalTime = 0;
            let interval = setInterval(function () {
                intervalTime++;
                $("#msgSpan").text("执行耗时:" + intervalTime + "毫秒");
            }, 1);
            post(ctx + "sqlManager/executeSqlNew", {'dataBaseName': activeDataSource, 'sqlText': sqlContent}, (res) => {
                window.clearInterval(interval);
                if (pageVisibility === 'hidden') {
                    sendNotification("SQL执行完成,请查看页面执行结果~");
                }
                if (!res) {
                    $("#msgSpan").text("ERROR:未知异常!");
                    $("#sqlTableDiv").hide();
                    return false;
                }
                if (res.code != 1) {
                    let msg = res.msg == null ? "空指针异常,请查看系统日志详情!" : res.msg;
                    layer.msg(msg, {icon: 2});
                    $("#msgSpan").text("ERROR:" + msg);
                    $("#sqlTableDiv").hide();
                    return false;
                }
                tableInit(res);
            });
            return false;
        });


        /**
         * 渲染数据表格
         * @param table
         * @param data
         * @returns {boolean}
         */
        let elementTabCount = 0;//tab总数

        function tableInit(res) {
            //删除历史所有tab
            for (let i = 0; i < elementTabCount; i++) {
                element.tabDelete('sqlTableTab', "sqlTable" + i);
            }
            $("#sqlTableDiv").hide();
            let data = res.dataList;
            elementTabCount = data.length;
            //执行语句仅返回条数
            if (res.sqlExecuteType != "SELECT" && res.sqlExecuteType != 'DESC') {
                let executeMsg = "";
                for (let i = 0; i < data.length; i++) {
                    if (executeMsg != "") {
                        executeMsg += "<br>";
                    }
                    let msg = data[i].code === "2" ? "执行失败:" + data[i].msg : "执行成功:" + data[i].data + "条";
                    executeMsg += "第【" + (i + 1) + "】条SQL语句 " + msg;
                }
                $("#msgSpan").html(executeMsg);
                return false;
            }
            for (let i = 0; i < data.length; i++) {
                if (data[i].code != 1) {
                    $("#msgSpan").append("结果集" + (i + 1) + "ERROR:" + data[i].msg + " \n ");
                    continue;
                }
                if (data[i].data.length === 0) {
                    $("#msgSpan").append("结果集" + (i + 1) + "：SQL执行成功,返回结果为空！ \n ");
                    continue;
                }
                $("#sqlTableDiv").show();
                //列头
                let headArr = Object.keys(data[i].data[0]);
                let head = [];
                for (let key in headArr) {
                    head.push({field: headArr[key], title: headArr[key], sort: true});
                }
                let rowsData = data[i].data;
                //2023.08.12 add兼容0条数据展示列头字段
                if (rowsData.length == 1) {
                    let distinctList = [...new Set(Object.values(data[i].data[0]))];
                    if (distinctList.length === 1 && distinctList[0] == "WEB_SQL_PLACEHOLDER") {
                        rowsData = [];
                    }
                }
                //add Tab
                let elId = "sqlTable" + i;
                element.tabAdd('sqlTableTab', {
                    title: '结果集' + (i + 1),
                    content: "<table class='layui-table' id='" + elId + "'></table>",
                    id: elId,
                    change: true
                })
                let toolbarHtml = "<div><span class=\"layui-badge\">" + head.length + "列</span> <span class=\"layui-badge layui-bg-green\">" + data[i].data.length + "条</span> <button class='layui-btn layui-btn-primary layui-border-purple layui-btn-xs' onclick='window.scrollTo({top: document.documentElement.scrollHeight, behavior: \"smooth\"})'>分页区</button></div>";
                table.render({
                    elem: '#' + elId
                    , cols: [head]
                    , data: rowsData
                    , title: '结果集' + (i + 1)
                    , loading: true
                    , height: 935
                    , cellMinWidth: 150
                    , limit: 20
                    , limits: [20, 500, 1000, 3000, 5000, 8000, 10000]
                    , even: true
                    , toolbar: toolbarHtml
                    , defaultToolbar: ['filter', 'exports', 'print']
                    , page: {prev: '上一页', next: '下一页', groups: 6}
                    , defaultContextmenu: false
                    , pagebar: '#table-page-pageBar'
                    , text: {none: '查询返回0条数据'}
                });
                table.resize(elId);
                rowContextmenu(elId, res);
                pageBar(elId, res);
            }
            window.scrollTo({
                top: 90,
                behavior: "smooth"
            });
            return false;
        }

        /**
         * 分页btn 增加返回顶部
         */
        function pageBar(id, res) {
            table.on('pagebar(' + id + ')', function (obj) {
                let eventValue = obj.event;
                if (eventValue === "footerBtn1") {
                    window.scrollTo({
                        top: 0,
                        behavior: "smooth"
                    });
                    return false;
                }
                if (eventValue === "footerBtn2") {
                    window.scrollTo({
                        top: 70,
                        behavior: "smooth"
                    });
                    return false;
                }
            });
        }

        /**
         * 表格增加点击事件
         */
        function rowContextmenu(id, res) {
            table.on('rowContextmenu(' + id + ')', function (obj) {
                let data = obj.data;
                let index = obj.index;
                dropdown.render({
                    trigger: 'contextmenu',
                    show: true,
                    data: [
                        {title: '复制JSON数据', id: 'json'}
                        //{title: 'INSERT语句', id: 'insert'}
                    ],
                    click: function (menuData, othis) {
                        if (menuData.id === "json") {
                            layer.alert(util.escape(JSON.stringify({data})));
                            return false;
                        }
                        //TODO 待处理拼接，暂时无法应对多条sql执行
                        if (menuData.id === "insert") {
                            layer.alert("insert into " + res.tableName + " values.....");
                            return false;
                        }
                    }
                });
            });
        }

        /**
         * tab删除事件,最后一个被删除是隐藏tableDiv
         */
        element.on('tabDelete(sqlTableTab)', function (data) {
            if ($("#lists li").length == 0) {
                $("#sqlTableDiv").hide();
            }
        });

        /**
         * tab切换事件,修正table表格单元格不对齐问题
         */
        element.on('tab(sqlTableTab)', function (data) {
            table.resize("sqlTable" + data.index);
        });

        /**
         * 动态悬浮功能菜单
         */
        document.addEventListener('scroll', function () {
            var navContainer = document.querySelector('.menus');
            if (window.pageYOffset > 0) {
                navContainer.classList.add('nav-container');
            } else {
                navContainer.classList.remove('nav-container');
            }
        });

        /**
         * 快捷键
         * @param event
         */
        document.onkeydown = function (event) {
            var e = event || window.event || arguments.callee.caller.arguments[0];
            if (e && e.keyCode == 119) {
                $("#execute").trigger('click');
            }
            if (e && e.keyCode == 118) {
                $("#newSqlOpen").trigger('click');
            }
            if (e && e.keyCode == 120) {
                $("#saveSqlText").trigger('click');
            }
        };

        /**
         * 检查页面是否隐藏
         */
        document.addEventListener('visibilitychange', function () {
            pageVisibility = document.visibilityState
        })
    })
    ;

</script>
</body>
</html>